﻿using API_Service.DBHelper;
using Dapper;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace API_Service.BizLogic.Kanban
{
    public class KBNService:BaseLogicService
    {

        public ExecutionResult<dynamic> GetEKBNInfo(string vLineName)
        {
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            if (String.IsNullOrEmpty(vLineName))
            {
                result.Status = false;
                result.Message = "线别不能为空.";

                return result;
            }

            List<dynamic> _lstKNBInfo = new List<dynamic>();
            //获取生产产品信息
            result = this.QueryKPNProductionInfo(vLineName);
            if (!result.Status)
                return result;
            _lstKNBInfo.Add(new { line_products = result.Datas });

            //获取产能信息
            result = this.QueryKPNOutputInfo(vLineName);
            if (!result.Status)
                return result;
            _lstKNBInfo.Add(new { class_output = result.Datas });

            //获取总产出信息
            result = this.QueryLineOutputSummary(result.Datas);
            if (!result.Status)
                return result;
            _lstKNBInfo.Add(new { output_summary = result.Datas });

            if (result.Status)
            {
                result.Status = true;
                result.Message = "Get KBN infor success.";
                result.Datas = _lstKNBInfo.ToArray();
            }

            return result;
        }
        private ExecutionResult<dynamic> QueryKPNOutputInfo(string vLineName)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            string _strInputGroup = "", _strOutputGroup = "";
            #region sql and parameters
            if (vLineName.Contains("PACK"))
            {
                _strInputGroup = " AND m.pack_input_group = b.group_name ";
                _strOutputGroup = " AND m.pack_output_group = b.group_name ";
            }
            else
            {
                _strInputGroup = " AND m.input_group = b.group_name ";
                _strOutputGroup = " AND m.output_group = b.group_name ";
            }
            string sql = @"select aa.*,CAST( ROUND( (CAST (aa.output_qty AS float) / aa.target_output_qty)*100,2)
	                                     AS NVARCHAR)+'%' AS output_rate 
                                from (
                                SELECT a.line_name,@production_date as production_date, a.start_time,a.end_time,a.target_output_qty,
                                (SELECT sum(b.pass_qty+b.repass_qty+b.fail_qty) FROM [dbo].[r_station_rec_t] as b,[dbo].[r_mo_base_t] as m
                                 WHERE b.work_date= @production_date AND b.mo_number = m.mo_no AND b.line_name = a.line_name  ";

            sql = sql+ _strInputGroup + @" AND b.work_section BETWEEN a.start_time and a.end_time) as input_qty,
                            (SELECT sum(b.pass_qty+b.repass_qty+b.fail_qty)  FROM [dbo].[r_station_rec_t] as b,[dbo].[r_mo_base_t] as m
                             WHERE b.work_date= @production_date AND b.mo_number = m.mo_no AND b.line_name = a.line_name ";
            sql = sql + _strOutputGroup + @" AND b.work_section BETWEEN a.start_time and a.end_time) as output_qty
                            FROM [dbo].[c_output_plan_t] AS a
                            WHERE a.line_name =@line_name) as aa
                            ORDER BY aa.start_time";
            #endregion 

            using(IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    result.Datas = conn.Query(sql, 
                        new { line_name = vLineName,
                            production_date = DateTime.Now.ToString("yyyyMMdd") }).ToArray();
                    if (result.Datas.Length > 0)
                    {
                        result.Status = true;
                        result.Message = "查询看板产出信息成功。";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "系统无看板产出目标配置信息。";
                    }
                    
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "-" + result.Message);
                }
                catch (Exception ex)
                {
                    result.Status = true;
                    result.Message = "查询看板产出信息异常。异常信息："+ex.Message;
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name, ex);
                }
            }

            return result;
        }

        private ExecutionResult<dynamic> QueryKPNProductionInfo(string vLineName)
        {
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            #region SQL and parameters
            string sql = @"SELECT c.model_name,c.sku_name,b.sku_no,b.cust_code,b.cust_sku,max(a.workdate_id) workdate_id
                         FROM [dbo].[r_station_rec_t] as a, [dbo].[r_mo_base_t] as b LEFT JOIN [dbo].[c_sku_base_info_t] as c on b.sku_no = c.sku_no 
                        WHERE a.mo_number = b.mo_no AND a.work_date=@production_date 
                        AND a.line_name=@line_name";
            if (vLineName.Contains("PACK"))
            {
                sql = sql + " AND a.group_name = b.pack_input_group";
            }
            else
            {
                sql = sql + " AND a.group_name = b.input_group";
            }
            sql = sql+ " GROUP BY c.model_name,c.sku_name,b.sku_no,b.cust_code,b.cust_sku";
            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    result.Datas = conn.Query(sql,
                        new
                        {
                            line_name = vLineName,
                            production_date = DateTime.Now.ToString("yyyyMMdd")
                        }).ToArray();
                    if (result.Datas.Length > 0)
                    {
                        result.Status = true;
                        result.Message = "查询看板产品信息成功。";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "系统无看板产品信息。";
                    }

                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "-" + result.Message);
                }
                catch (Exception ex)
                {
                    result.Status = true;
                    result.Message = "查询看板产品信息异常。异常信息：" + ex.Message;
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }

        private ExecutionResult<dynamic> QueryLineOutputSummary(dynamic[] vLineOutputInfo)
        {
            ExecutionResult<dynamic> result;
            List<dynamic> lstOutputSummary = new List<dynamic>();
            result = new ExecutionResult<dynamic>();
           
            if(vLineOutputInfo != null)
            {
                string line_name = "", production_date = "", output_rate = "";
                Int32 ttl_target = 0, ttl_pass_qty = 0, ttl_input = 0;
                
                foreach(var lineoutput in vLineOutputInfo)
                {
                    if (String.IsNullOrEmpty(line_name))
                    {
                        line_name = lineoutput.line_name;
                    }
                    if (String.IsNullOrEmpty(production_date))
                    {
                        production_date = lineoutput.production_date;
                    }
                    ttl_target += lineoutput.target_output_qty == null ? 0 : lineoutput.target_output_qty;
                    ttl_pass_qty += lineoutput.output_qty == null ? 0 : lineoutput.output_qty;
                    ttl_input += lineoutput.input_qty == null ? 0 : lineoutput.input_qty;
                }
                ttl_target = ttl_target == 0 ? 1 : ttl_target;
                var rate = Math.Round((Convert.ToDouble(ttl_pass_qty) / ttl_target) * 100, 2);
                output_rate = rate.ToString() + "%";

                var lineoutInfo = new
                {
                    line_name = line_name,
                    production_date = production_date,
                    ttl_target = ttl_target,
                    ttl_input = ttl_input,
                    ttl_pass_qty = ttl_pass_qty,
                    output_rate = output_rate
                };

                lstOutputSummary.Add(lineoutInfo);
                result.Status = true;
                result.Message = "Qery Line output summary success";
                result.Datas = lstOutputSummary.ToArray();
            }
            else
            {
                result.Status = false;
                result.Message = "None Line output data";
            }

            return result;
        }
    }
}